#!/bin/bash

APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 

dim_user_zip="
insert overwrite table edu.dim_user_zip partition (dt='9999-12-31')
select
    data.id,
    data.login_name,
    data.nick_name,
    md5(data.real_name),
    md5(data.phone_num),
    md5(data.email),
    data.user_level,
    data.birthday,
    data.gender,
    data.create_time,
    data.operate_time,
    '2022-07-05' start_date,
    '9999-12-31' end_date
 from edu.ods_user_info_inc
 where dt='$do_date'
 and type='bootstrap-insert';
"

dim_province_full="
insert overwrite table edu.dim_province_full partition (dt='$do_date')
select
id,
name,
region_id,
area_code,
iso_code,
iso_3166_2
from 
edu.ods_base_province_full
where dt='$do_date'
"
dim_chapter_full="
insert overwrite table edu.dim_chapter_full partition(dt='$do_date')
select
    chapter.id as chapter_id,
    chapter.chapter_name as chapter_name,
    video.id as video_id,
    video.video_name as video_name,
    video.video_status,
    during_sec,
    video_size,
    video.video_source_id,
    source_site,
    chapter.course_id,
    course_name,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name,
    chapter.is_free,
    chapter.create_time,
    chapter.update_time
from (
    select *
    from edu.ods_chapter_info_full
    where dt = '$do_date'
) chapter
left join (
    select *
    from edu.ods_video_info_full
    where dt = '$do_date'
) video
on chapter.video_id = video.id
left join (
    select *
    from edu.ods_base_source_full
    where dt = '$do_date'
) source
on video.video_source_id = source.id
left join (
    select *
    from edu.ods_course_info_full
    where dt = '$do_date'
) course
on chapter.course_id = course.id
left join (
    select * 
    from edu.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from edu.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"
dim_test_full="
insert overwrite table edu.dim_test_full partition(dt='$do_date')
select 
    paper_question.id as id,
    paper_question.question_id,
    paper_question.score,
    paper_question.create_time,
    paper.id as paper_id,
    paper.paper_title,
    paper.course_id,
    course.course_name,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name
from (
    select *
    from edu.ods_test_paper_question_full
    where dt = '$do_date'
) paper_question
left join (
    select *
    from edu.ods_test_paper_full
    where dt = '$do_date'
) paper
on paper_question.paper_id = paper.id
left join (
    select * 
    from edu.ods_course_info_full
    where dt = '$do_date'
) course
on paper.course_id = course.id
left join (
    select *
    from edu.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from edu.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"
dim_course_full="
insert overwrite table edu.dim_course_full partition(dt='$do_date')
select
    course.id as course_id,
    course_name,
    chapter_num,
    origin_price,
    reduce_amount,
    actual_price,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name
from (
    select *
    from edu.ods_course_info_full
    where dt = '$do_date'
) course
left join (
    select *
    from edu.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from edu.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_chapter_full")
    hive -e "$dim_chapter_full"
;;
"dim_test_full")
    hive -e "$dim_test_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"all")
    hive -e "$dim_user_zip$dim_province_full$dim_chapter_full$dim_test_full$dim_course_full"
;;
esac
